#Region "Comment"

'  Module                       :    Common
'  Purpose                      :    Defines the common functions.
'  Tables Referred              :    N/A
'  Tables Modified              :    N/A
'  Chronological Development
'Class                  Date            Contribution
'<Globals              4/18/07             Parminder> 

#End Region

Imports System.Data.SqlClient
Imports Crystal.DatabaseManagement.BusinessLayer
Imports System.Configuration
Imports System.Configuration.ConfigurationManager
Imports Infragistics.Win.DateTimeEditor
Imports Infragistics.Win.UltraWinGrid
Imports System.Text.RegularExpressions
Imports System.Runtime.CompilerServices
Imports Infragistics.Win
Imports System.Xml
Imports System.Windows.Forms.Form
Imports Crystal.CommonDBManagement
Imports Crystal.CLL.CommonCLL
Public Module mdlCommon

#Region "Declare"
    'Master table-------------------------------------------------
    Public frmmstTable As frmAddModifyRefTable = Nothing
    '-------------------------------------------------------------
#End Region

#Region "New Declare"

    'ID value for Select value that is used as first row in most of the drop downs
    Public m_strSelectIDValue As Integer = -1
    'ID value for Other value that is used as last row in most of the drop downs
    Public m_strOtherIDValue As Integer = -2

    Public m_strNewIDValue = -3
    'Interger value that is passed to Stored Procedure to pick all records off the query
    Public m_strIntValueToPickAllRecords As Integer = -1
    'Text value for Select value that is used as first row in most of the drop downs
    Public m_strSelectTextValue As String = "---Select---"
    Public m_strNewTextValue As String = "--New--"
    'Text value for Other value that is used as last row in most of the drop downs
    Public m_strOtherTextValue As String = "---Other---"
    Public m_strBlankTextValue As String = "         "
    Public m_strBlankIdValue As String = -6

    'Text value for Title value that is used as first row in title drop down
    Public m_strMsgBoxCaption As String = "Crystal"
#End Region

#Region "Enumerator" 'These are not subject to change, please update respctive tables in case of modifications
    Enum MessageBoxIcons
        Information = 1
        Warning = 2
        [Error] = 2
    End Enum

#End Region

#Region "General"

    Public Sub SetTabSettings(ByVal e As System.Windows.Forms.DrawItemEventArgs, ByVal Tab As System.Windows.Forms.TabControl)
        Dim g As Graphics = e.Graphics
        Dim tp As TabPage = Tab.TabPages(e.Index)
        Dim br As Brush
        Dim sf As New StringFormat
        Dim r As New RectangleF(e.Bounds.X, e.Bounds.Y + 2, e.Bounds.Width + 2, e.Bounds.Height - 2)
        sf.Alignment = StringAlignment.Center
        Dim strTitle As String = tp.Text
        'If the current index is the Selected Index, change the color
        If Tab.SelectedIndex = e.Index Then
            'this is the background color of the tabpage
            'you could make this a stndard color for the selected page
            'br = New SolidBrush(mdlCommon.GetFormBackColor)
            'this is the background color of the tab page
            'g.FillRectangle(br, e.Bounds)
            ''this is the background color of the tab page
            ''you could make this a stndard color for the selected page
            ''br = New SolidBrush(mdlCommon.GetLabelForeColor)
            'g.DrawString(strTitle, Tab.Font, br, r, sf)
        Else
            'these are the standard colors for the unselected tab pages
            'br = New SolidBrush(mdlCommon.GetFormBackColor)
            'g.FillRectangle(br, e.Bounds)
            '' br = New SolidBrush(mdlCommon.GetLabelForeColor)
            'g.DrawString(strTitle, Tab.Font, br, r, sf)
        End If
    End Sub

    ''' <summary>
    ''' return Color schema
    ''' </summary>
    ''' <param name="Val"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetARGBDetails(ByVal Val As String) As Color

        Dim seperator() As String = {","}
        Dim colorschema() As String = Val.Split(seperator, StringSplitOptions.RemoveEmptyEntries)
        If (colorschema.Length = 4) Then
            If (IsNumeric(colorschema(0).ToString()) And IsNumeric(colorschema(1).ToString()) And IsNumeric(colorschema(2).ToString()) And IsNumeric(colorschema(3).ToString())) Then
                Return System.Drawing.Color.FromArgb(colorschema(0).ToString(), colorschema(1).ToString(), colorschema(2).ToString(), colorschema(3).ToString())
            Else
                Return Color.Empty
            End If
        Else
            Return Color.Empty
        End If

    End Function

    ''' <summary>
    ''' Dertermines whether to enable the teh proper button or not
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function EnableProper(ByVal SearchString As String) As Boolean
        Dim Seperator() As String = {" "}
        Dim str() As String = SearchString.Trim().Split(Seperator, System.StringSplitOptions.RemoveEmptyEntries)
        Dim Contains As Boolean = False
        Dim regex As New System.Text.RegularExpressions.Regex("^[A-Z]+$")
        For Each Val As String In str
            If (regex.IsMatch(Val)) Then
                Contains = True
                Return True
            End If
        Next
        Return False
    End Function

    ''' <summary>
    ''' converts to title case
    ''' </summary>
    ''' <param name="SearchString"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AutoProperConversion(ByVal SearchString As String) As String
        Return StrConv(SearchString.ToString().Trim(), VbStrConv.ProperCase)
    End Function


    ''' <summary>
    ''' The following function is specifically for Map Investor and Map Fund in Fund and Investor Tab
    ''' </summary>
    ''' <param name="grid"></param>
    ''' <param name="GrdRow"></param>
    ''' <remarks></remarks>
    Public Sub SetExcelSearchedrecordOnTop(ByVal grid As UltraGrid, ByVal GrdRow As UltraGridRow)
        Dim col As RowScrollRegionsCollection = Nothing
        '***************************************************************************************
        'If the investor is not selected in the search, then no need to ammend the progressbar
        If (GrdRow Is Nothing) Then Exit Sub
        'Finished

        '***********************************************
        'The following code shall split the Grid and provide us that part in which we selected 
        'the investor excel record'
        col = grid.DisplayLayout.RowScrollRegions
        'Finished

        If (col Is Nothing) Then Exit Sub
        If (grid Is Nothing) Then Exit Sub
        If (grid.ActiveRow Is Nothing) Then Exit Sub
        Try
            'The following loop shall set the selected investor to top
            For i As Integer = 0 To grid.ActiveRow.Index - (col(0).ScrollPosition - 1) - 3
                col(0).Scroll(RowScrollAction.LineDown)
            Next
            'Finished
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in SetExcelSearchedrecordOnTop in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
            MessageBox.Show(ex.Message, m_strMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try


    End Sub
    ''' <summary>
    ''' To show a message in Message Box
    ''' </summary>
    ''' <param name="strMessage">Message to be shown</param>
    ''' <remarks></remarks>
    Public Sub ShowMessage(ByVal strMessage As String, ByVal messageboxicon As MessageBoxIcons)
        Select Case messageboxicon
            Case MessageBoxIcons.Information
                MessageBox.Show(strMessage, m_strMsgBoxCaption, MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Information)
            Case MessageBoxIcons.Warning
                MessageBox.Show(strMessage, m_strMsgBoxCaption, MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Warning)
            Case MessageBoxIcons.Error
                MessageBox.Show(strMessage, m_strMsgBoxCaption, MessageBoxButtons.OK, Windows.Forms.MessageBoxIcon.Error)
        End Select
    End Sub
#End Region

    Public Function FocusTheExistingInstanceOfForms(ByVal Filename As String, ByVal windowstate As FormWindowState) As Boolean
        Dim frmimpfnd As ImportFund = Nothing
        Dim frmImpInv As frmUploadInvestor = Nothing
        Dim investor As frmInvestorManagement = Nothing
        Dim fund As FundManagement = Nothing
        Dim master As frmAddModifyRefTable = Nothing
        Dim delInv As DeleteInvestor = Nothing
        Dim delFund As frmDeleteFund = Nothing
        'Dim lfrm As l
        'Dim frmChangeUserStatus As frmChangeUserLoginStatus = Nothing
        Try

            For i As Integer = 0 To My.Application.OpenForms.Count - 1
                Select Case Filename

                    Case "frmImportFund"
                        If (My.Application.OpenForms(i).Name = Filename) Then
                            frmimpfnd = My.Application.OpenForms(i)
                            If (windowstate = FormWindowState.Maximized) Then
                                frmimpfnd.WindowState = FormWindowState.Maximized
                            End If
                            If (windowstate = FormWindowState.Normal) Then
                                frmimpfnd.WindowState = FormWindowState.Normal
                            End If
                            frmimpfnd.Focus()
                            Return True
                        End If
                    Case "frmImportInvestor"
                        If (My.Application.OpenForms(i).Name = Filename) Then
                            frmImpInv = My.Application.OpenForms(i)
                            If (windowstate = FormWindowState.Maximized) Then
                                frmImpInv.WindowState = FormWindowState.Maximized
                            End If
                            If (windowstate = FormWindowState.Normal) Then
                                frmImpInv.WindowState = FormWindowState.Normal
                            End If
                            frmImpInv.Focus()
                            Return True
                        End If
                    Case "frmDeleteFund"
                        If (My.Application.OpenForms(i).Name = Filename) Then
                            delFund = My.Application.OpenForms(i)
                            If (windowstate = FormWindowState.Maximized) Then
                                delFund.WindowState = FormWindowState.Maximized
                            End If
                            If (windowstate = FormWindowState.Normal) Then
                                delFund.WindowState = FormWindowState.Normal
                            End If
                            delFund.Focus()
                            Return True
                        End If
                    Case "DeleteInvestor"
                        If (My.Application.OpenForms(i).Name = Filename) Then
                            delInv = My.Application.OpenForms(i)
                            If (windowstate = FormWindowState.Maximized) Then
                                delInv.WindowState = FormWindowState.Maximized
                            End If
                            If (windowstate = FormWindowState.Normal) Then
                                delInv.WindowState = FormWindowState.Normal
                            End If
                            delInv.Focus()
                            Return True
                        End If
                    Case "frmInvestorManagement"
                        If (My.Application.OpenForms(i).Name = Filename) Then
                            investor = My.Application.OpenForms(i)
                            If (windowstate = FormWindowState.Maximized) Then
                                investor.WindowState = FormWindowState.Maximized
                            End If
                            If (windowstate = FormWindowState.Normal) Then
                                investor.WindowState = FormWindowState.Normal
                            End If
                            investor.Focus()
                            Return True
                        End If
                    Case "frmFundManagement"
                        If (My.Application.OpenForms(i).Name = Filename) Then
                            fund = My.Application.OpenForms(i)
                            If (windowstate = FormWindowState.Maximized) Then
                                fund.WindowState = FormWindowState.Maximized
                            End If
                            If (windowstate = FormWindowState.Normal) Then
                                fund.WindowState = FormWindowState.Normal
                            End If
                            fund.Focus()
                            Return True
                        End If
                    Case "frmAddModifyRefTable"
                        If (My.Application.OpenForms(i).Name = Filename) Then
                            master = My.Application.OpenForms(i)
                            If (windowstate = FormWindowState.Maximized) Then
                                master.WindowState = FormWindowState.Maximized
                            End If
                            If (windowstate = FormWindowState.Normal) Then
                                master.WindowState = FormWindowState.Normal
                            End If
                            master.Focus()
                            Return True
                        End If
                    Case "ImportFund"
                        If (My.Application.OpenForms(i).Name = Filename) Then
                            master = My.Application.OpenForms(i)
                            If (windowstate = FormWindowState.Maximized) Then
                                master.WindowState = FormWindowState.Maximized
                            End If
                            If (windowstate = FormWindowState.Normal) Then
                                master.WindowState = FormWindowState.Normal
                            End If
                            master.Focus()
                            Return True
                        End If
                End Select
            Next
        Catch ex As Exception
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in FocusTheExistingInstanceOfForm in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try
    End Function
    Public Function FocusTheExistingInstanceOfForm(ByVal Filename As String, ByVal windowstate As FormWindowState) As Boolean
        Dim objImportInvestor As frmUploadInvestor = Nothing
        For i As Integer = 0 To My.Application.OpenForms.Count - 1
            Select Case Filename
                Case "frmUploadInvestor"
                    If (My.Application.OpenForms(i).Name = Filename) Then
                        objImportInvestor = My.Application.OpenForms(i)
                        objImportInvestor.BringToFront()

                    End If
            End Select

        Next
    End Function

    Public Function FocusTheExistingInstanceOfForm(ByVal id As Integer, ByVal Filename As String, ByVal windowstate As FormWindowState) As Boolean
        Dim objInv As frmAddModifyInvestor = Nothing
        Dim objFund As AddEditFund = Nothing
        Dim objLKP As frmAddEditLKPTable = Nothing
        Try
            For i As Integer = 0 To My.Application.OpenForms.Count - 1
                Select Case Filename

                    Case "frmAddModifyInvestor"
                        If (My.Application.OpenForms(i).Name = Filename) Then
                            objInv = My.Application.OpenForms(i)
                            If (objInv.InvestorId = id) Then
                                If (windowstate = FormWindowState.Maximized) Then
                                    objInv.WindowState = FormWindowState.Maximized
                                End If
                                If (windowstate = FormWindowState.Normal) Then
                                    objInv.WindowState = FormWindowState.Normal
                                End If
                                objInv.Focus()
                                Return True
                            End If
                        End If
                    Case "AddEditFund"
                        If (My.Application.OpenForms(i).Name = Filename) Then
                            objFund = My.Application.OpenForms(i)
                            If (objFund.FundId = id) Then
                                If (windowstate = FormWindowState.Maximized) Then
                                    objFund.WindowState = FormWindowState.Maximized
                                End If
                                If (windowstate = FormWindowState.Normal) Then
                                    objFund.WindowState = FormWindowState.Normal
                                End If
                                objFund.Focus()
                                Return True
                            End If
                        End If

                    Case "frmAddEditLKPTable"
                        If (My.Application.OpenForms(i).Name = Filename) Then
                            objLKP = My.Application.OpenForms(i)
                            If (windowstate = FormWindowState.Maximized) Then
                                objLKP.WindowState = FormWindowState.Maximized
                            End If
                            If (windowstate = FormWindowState.Normal) Then
                                objLKP.WindowState = FormWindowState.Normal
                            End If
                            objLKP.Focus()
                            Return True
                        End If
                End Select
            Next
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in FocusTheExistingInstanceOfForm in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try

    End Function

    Public Function checkdate(ByVal grid As Infragistics.Win.UltraWinGrid.UltraGrid, ByVal column_name As String, ByRef dateVal As String) As Boolean
        Dim str() As String
        Try
            If (grid.ActiveRow.Cells(column_name).EditorResolved.IsInEditMode) Then
                str = grid.ActiveRow.Cells(column_name).EditorResolved.CurrentEditText.ToString().Split("/")
                If (str.Length <> 3) Then
                    Return False
                End If
                If (Not IsNumeric(str(0)) Or Not IsNumeric(str(1)) Or Not IsNumeric(str(2))) Then
                    Return False
                End If
                If (Convert.ToInt16(str(0).ToString()) = 0 Or Convert.ToInt16(str(1).ToString()) Or Convert.ToInt16(str(2).ToString())) Then
                    Return False
                End If
                If (IsDate(grid.ActiveRow.Cells(column_name).EditorResolved.Value)) Then
                    dateVal = grid.ActiveRow.Cells(column_name).EditorResolved.Value
                    Return True
                Else
                    dateVal = ""
                    Return False
                End If
            Else
                If (IsDate(grid.ActiveRow.Cells(column_name).Value)) Then
                    dateVal = grid.ActiveRow.Cells(column_name).Value
                    Return True
                Else
                    dateVal = ""
                    Return False
                End If
            End If
            Return True
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in checkdate in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
            Return False
        End Try

    End Function

    ''' <summary>
    ''' To check/uncheck permissions from Permission Grid
    ''' </summary>
    ''' <remarks></remarks>
    ''' 

    Public Sub CheckUncheckPermissions(ByRef ultraGridRow As UltraGrid, Optional ByRef IsChecked As Boolean = False)
        Try
            If CheckNothingUltraGridActiveCell(ultraGridRow) = False Then Exit Sub
            If ultraGridRow.ActiveCell Is Nothing Then Exit Sub
            If ultraGridRow.ActiveCell.Column.Header.Caption = "Category" OrElse ultraGridRow.ActiveCell.Column.Header.Caption = "Category Permissions" Then Exit Sub
            Dim IsAnyFalseChildrow As Boolean = False
            'ultraGridRow.ActiveRow.Index
            'ultraGridRow.DataSource
            'ultraGridRow.ActiveRow.Cells("Full").Text 
            IsChecked = True
            If ultraGridRow.ActiveRow.HasChild = False Then
                If ultraGridRow.ActiveRow.ParentRow IsNot Nothing Then
                    If ultraGridRow.ActiveRow.Cells(ultraGridRow.ActiveCell.Column.Header.Caption).Text = "True" Then
                        ultraGridRow.ActiveRow.ParentRow.Cells(ultraGridRow.ActiveCell.Column.Header.Caption).Value = "False"
                        'For Each dr As UltraGridRow In ultraGridRow.ActiveRow.ParentRow.ChildBands.Item(0).Rows
                        '    dr.Cells(ultraGridRow.ActiveCell.Column.Header.Caption).Value = "False"
                        'Next
                    Else
                        For Each dr As UltraGridRow In ultraGridRow.ActiveRow.ParentRow.ChildBands.Item(0).Rows
                            If dr.Index <> ultraGridRow.ActiveRow.Index Then
                                If dr.Cells(ultraGridRow.ActiveCell.Column.Header.Caption).Value = "False" Then
                                    IsAnyFalseChildrow = True
                                End If
                            End If
                        Next
                        If IsAnyFalseChildrow = False Then
                            ultraGridRow.ActiveRow.ParentRow.Cells(ultraGridRow.ActiveCell.Column.Header.Caption).Value = "True"
                        End If
                    End If
                End If
            End If
            If ultraGridRow.ActiveCell.Column.Header.Caption = "Select" Then
                If ultraGridRow.ActiveRow.Cells("Select").Text = "True" Then
                    ultraGridRow.ActiveRow.Cells("Select").Value = "False"
                    'ultraGridRow.ActiveRow.Cells("Select").Value = "False"
                    'ultraGridRow.ActiveRow.Cells("Modify").Value = "False"
                    ' ultraGridRow.ActiveRow.Cells("Delete").Value = "False"
                    ' ultraGridRow.ActiveRow.Cells("Visible").Value = "False"
                    If ultraGridRow.ActiveRow.HasChild Then
                        For Each dr As UltraGridRow In ultraGridRow.ActiveRow.ChildBands.Item(0).Rows
                            'dr.Cells("Full").Value = "False"
                            dr.Cells("Select").Value = "False"
                            'dr.Cells("Modify").Value = "False"
                            'dr.Cells("Delete").Value = "False"
                            'dr.Cells("Visible").Value = "False"
                        Next
                    End If
                Else
                    ultraGridRow.ActiveRow.Cells("Select").Value = "True"
                    'ultraGridRow.ActiveRow.Cells("Add").Value = "True"
                    'ultraGridRow.ActiveRow.Cells("Modify").Value = "True"
                    'ultraGridRow.ActiveRow.Cells("Delete").Value = "True"
                    'ultraGridRow.ActiveRow.Cells("Visible").Value = "True"
                    If ultraGridRow.ActiveRow.HasChild Then
                        For Each dr As UltraGridRow In ultraGridRow.ActiveRow.ChildBands.Item(0).Rows
                            'dr.Cells("Full").Value = "True"
                            dr.Cells("Select").Value = "True"
                            'dr.Cells("Modify").Value = "True"
                            'dr.Cells("Delete").Value = "True"
                            'dr.Cells("Visible").Value = "True"
                        Next
                    End If
                End If
            End If
            CType(ultraGridRow.DataSource, DataSet).AcceptChanges()
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in CheckUncheckPermissions in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing

        End Try
    End Sub

#Region "UltraGrid"

    ''' <summary>
    ''' Check Active Row and Rows of Ultragrid for NOTHING
    ''' </summary>
    ''' <param name="grdUltraGrid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function CheckNothingUltraGridActiveRow(ByVal grdUltraGrid As UltraGrid) As Boolean
        Try
            If grdUltraGrid.Rows Is Nothing Then Return False
            If grdUltraGrid.ActiveRow Is Nothing Then Return False
            Return True
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in CheckNothingUltraGridActiveRow in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing

            Throw ex
        End Try
    End Function


    ''' <summary>
    ''' To Hide a column of a grid
    ''' </summary>
    ''' <param name="ultraGridRow">Grid Name</param>
    ''' <param name="columnName">Column to hide</param>
    ''' <remarks></remarks>
    Public Sub HideUltraGridColumn(ByRef ultraGridRow As UltraGrid, ByVal columnName As String)
        If ultraGridRow.Rows.Band.Columns.Exists(columnName) Then
            ultraGridRow.Rows.Band().Columns(columnName).Hidden = True
        End If
    End Sub

    ''' <summary>
    ''' To set width of a column
    ''' </summary>
    ''' <param name="ultraGrid"></param>
    ''' <param name="columnName"></param>
    ''' <param name="columnWidth"></param>
    ''' <remarks></remarks>
    Public Sub SetUltraGridColumnWidth(ByRef ultraGrid As UltraGrid, ByVal columnName As String, ByVal columnWidth As Integer)
        If ultraGrid.Rows.Band.Columns.Exists(columnName) Then
            ultraGrid.Rows.Band().Columns(columnName).Width = columnWidth
        End If
    End Sub

    Public Sub AddCheckboxColInUltraGrid(ByRef ultraGrid As UltraGrid, ByVal band As Integer, ByVal columnName As String, ByVal columnindex As Int16, ByVal columnWidth As Integer)
        Try
            If ultraGrid.DisplayLayout.Bands(band).Columns.Exists(columnName) Then Exit Sub
            ultraGrid.DisplayLayout.Bands(band).Columns.Insert(columnindex, columnName)
            ultraGrid.DisplayLayout.Bands(band).Columns(columnName).DataType = GetType(Boolean)
            ultraGrid.DisplayLayout.Bands(band).Columns(columnName).Style = Infragistics.Win.UltraWinGrid.ColumnStyle.CheckBox
            If columnWidth <> 0 Then
                ultraGrid.DisplayLayout.Bands(band).Columns(columnName).Width = columnWidth
            End If
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in AddCheckboxColInUltraGrid in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try

    End Sub

#End Region

#Region "General"

    ''' <summary>
    '''  Validate Email Address
    ''' </summary>
    ''' <param name="lstrEmailAddress"> for validation purpose</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    ''' 
    Function EmailAddressCheck(ByVal lstrEmailAddress As String) As Boolean
        Try

            Dim lstrPattern As String = "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"
            Dim emailAddressMatch As Match = Regex.Match(lstrEmailAddress, lstrPattern)
            If emailAddressMatch.Success Then
                EmailAddressCheck = True
            Else
                MessageBox.Show("Please enter valid Email address.", m_strMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Warning)
                EmailAddressCheck = False
            End If
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in EmailAddressCheck in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try
    End Function

#End Region

#Region "Functions"

    ' ''' <summary>
    ' ''' To check if text box is empty.
    ' ''' </summary>
    ' ''' <param name="strText"></param>
    ' ''' <returns></returns>
    ' ''' <remarks></remarks>

    'Public Function IsEmpty(ByVal strText As String) As Boolean
    '    If strText.Trim = "" Then
    '        Return True
    '    Else
    '        Return False
    '    End If
    'End Function

    Public Function intCalculatePriority(ByVal pstrPriority As String) As Integer
        Select Case pstrPriority
            Case "Normal"
                Return 0
            Case "Low"
                Return 1
            Case "High"
                Return 2
            Case Else
                Return 0
        End Select
    End Function

    ''' <summary>
    ''' Returns true if text in one text box is not equal to the other.
    ''' </summary>
    ''' <param name="strText1"></param>
    ''' <param name="strText2"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function IsSame(ByVal strText1 As String, ByVal strText2 As String) As Boolean
        If strText1.Trim <> strText2.Trim Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' To check if value is selected or not.
    ''' </summary>
    ''' <param name="cmbo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function IsvalueSelected(ByVal cmbo As System.Windows.Forms.ComboBox) As Boolean
        If cmbo.SelectedIndex = -1 Or cmbo.SelectedValue = -1 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' To check if Combo Text is Selected
    ''' </summary>
    ''' <param name="cmbo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function IsComboTextSelected(ByVal cmbo As System.Windows.Forms.ComboBox) As Boolean
        If cmbo.SelectedIndex = -1 Or cmbo.SelectedValue = "0" Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Function to set the format of Date
    ''' </summary>
    ''' <param name="DtCombo"></param>
    ''' <remarks></remarks>

    Public Sub SetDateFormat(ByVal DtCombo As Infragistics.Win.UltraWinEditors.UltraDateTimeEditor)
        'DtCombo.FormatString = BLLmdlCommon.DateFormat
        DtCombo.FormatString = Replace(BusinessLayer.BLLmdlCommon.DateFormat, "//", "/")
    End Sub

    Public Sub SetDateFormat(ByVal DtCombo As Windows.Forms.DateTimePicker)
        DtCombo.Format = DateTimePickerFormat.Custom
        DtCombo.CustomFormat = Replace(BusinessLayer.BLLmdlCommon.DateFormat, "//", "/")
    End Sub

    ''' <summary>
    ''' Function to get Total Fund Records.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function TotalFundsRecords() As Integer
        'Dim loHistory As New Object
        'Dim dtTotalRecords As New DataTable
        'Dim lObjImportFndHistory As New cImportHistory
        'loHistory = lObjImportFndHistory.CountHistoryFundsRecords()
        'dtTotalRecords = CType(loHistory, DataSet).Tables(0)
        'If CheckNothingDataTable(dtTotalRecords) Then
        '    Return 0
        'Else
        '    Return dtTotalRecords.Rows(0).Item(0)
        'End If
    End Function

    ''' <summary>
    ''' This function checks whether the date is valid
    '''  It should check if the year is leap year and check if the date selected is
    '''  valid
    ''' </summary>
    ''' <param name="sDate"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Private Function ValidateDate(ByVal sDate)
        Dim aMonth
        Dim aLeapMonth
        Dim aDateOfBirth
        Dim iDay, iMonth, iYear
        Dim Array As Array = Nothing
        ValidateDate = False
        Try
            'Days in month of a normal year
            aMonth = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)

            'Days in month of a leap year
            aLeapMonth = Array(31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)

            'Get the day, month and year
            aDateOfBirth = Split(sDate, "/")
            iDay = CInt(aDateOfBirth(0))
            iMonth = CInt(aDateOfBirth(1))
            iYear = CInt(aDateOfBirth(2))

            'If all the items - day, month, year are selected, do the validation....else return
            'Note : The month array starts from 0
            If (iDay <> 0) And (iMonth <> 0) And (iYear <> 0) Then
                If ((iYear Mod 4) = 0) Then
                    If (((iYear Mod 100) = 0) And ((iYear Mod 400) <> 0)) Then 'Normal year
                        'Check if the day entered is valid
                        If (iDay) <= aMonth(iMonth - 1) Then ValidateDate = True
                    Else 'Leap year
                        If (iDay) <= aLeapMonth(iMonth - 1) Then ValidateDate = True
                    End If
                Else 'Normal year
                    If (iDay) <= aMonth(iMonth - 1) Then ValidateDate = True
                End If
            End If

        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in ValidateDate in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try

    End Function

    Public Sub CountWinProcesses(ByRef parrWinProcesses As ArrayList)
        Try


            Dim WordProcesses() As Process
            Dim ProcessInfo As Diagnostics.Process
            Dim lhtWinProcesses As Hashtable
            WordProcesses = Process.GetProcessesByName("WINWORD")
            For Each ProcessInfo In WordProcesses
                lhtWinProcesses = New Hashtable
                lhtWinProcesses.Item("ProcessID") = ProcessInfo.Id
                parrWinProcesses.Add(lhtWinProcesses)
            Next
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in CountWinProcesses in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Kiil the newly  word Process 
    ''' </summary>
    ''' <param name="parrWinProcesses"></param>
    ''' <remarks></remarks>
    '''
    Public Sub KillWinProcesses(ByVal parrWinProcesses As ArrayList)
        Dim WordProcesses() As Process
        Try
            WordProcesses = Process.GetProcessesByName("WINWORD")
            Dim lintWinProcessCount As Integer = WordProcesses.Length
            Dim lintCounter As Integer = 0
            While lintCounter < lintWinProcessCount
                If parrWinProcesses.Count = 0 Then
                    WordProcesses(lintCounter).Kill()
                    lintCounter += 1
                    Continue While
                End If
                Dim lblnIsExist As Boolean = False
                For Each lhtWinProcess As Hashtable In parrWinProcesses
                    If WordProcesses(lintCounter).Id = lhtWinProcess.Item("ProcessID") Then
                        lblnIsExist = True
                        lintCounter += 1
                        Continue While
                    End If
                    If lblnIsExist = False Then
                        WordProcesses(lintCounter).Kill()
                        lintCounter += 1
                        Continue While
                    End If
                Next
                lintCounter += 1
            End While
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in KillWinProcesses in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try

    End Sub

    ''' <summary>
    ''' AutoSize the columns Of grid
    ''' </summary>
    ''' <param name="grdExcel"></param>
    ''' <remarks></remarks>

    Public Sub FormatGrid(ByRef grdExcel As UltraGrid)
        Dim GridCol As UltraGridColumn
        Try
            If (grdExcel.DisplayLayout Is Nothing) Then Exit Sub
            grdExcel.DisplayLayout.Bands(0).Override.ColumnAutoSizeMode = ColumnAutoSizeMode.AllRowsInBand
            grdExcel.DisplayLayout.Bands(0).Override.AllowRowLayoutCellSpanSizing = Infragistics.Win.Layout.GridBagLayoutAllowSpanSizing.None
            grdExcel.DisplayLayout.Bands(0).Override.CellMultiLine = Infragistics.Win.DefaultableBoolean.False
            grdExcel.DisplayLayout.Bands(0).Override.DefaultColWidth = 0

            Dim rowCount As Integer = grdExcel.Rows.Count
            If (rowCount > 0) Then
                For Each GridCol In grdExcel.DisplayLayout.Bands(0).Columns
                    GridCol.PerformAutoResize(rowCount)
                Next
            End If
        Catch ex As Exception

            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in FormatGrid in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        Finally
            GridCol = Nothing
        End Try
    End Sub


    Public Sub CountRunningWinProcesses(ByRef parrRunningWinProcesses As ArrayList, ByRef parrNewWinProcesses As ArrayList, ByVal pblnIsAlreadyRunning As Boolean)
        Dim WordProcesses() As Process
        Dim ProcessInfo As Diagnostics.Process
        Dim lhtWinProcesses As Hashtable
        Dim lintProcessCounter As Integer = 0
        Try
            WordProcesses = Process.GetProcessesByName("WINWORD")
            If pblnIsAlreadyRunning Then
                For Each ProcessInfo In WordProcesses
                    lintProcessCounter = 0
                    While lintProcessCounter < parrNewWinProcesses.Count
                        If CType(parrNewWinProcesses(lintProcessCounter), Hashtable).Item("ProcessID") = ProcessInfo.Id Then
                            lintProcessCounter += 1
                            Continue For
                        End If
                        lintProcessCounter += 1
                    End While

                    lintProcessCounter = 0
                    While lintProcessCounter < parrRunningWinProcesses.Count
                        If CType(parrRunningWinProcesses(lintProcessCounter), Hashtable).Item("ProcessID") = ProcessInfo.Id Then
                            Continue For
                        End If
                        lintProcessCounter += 1
                    End While

                    lhtWinProcesses = New Hashtable
                    lhtWinProcesses.Item("ProcessID") = ProcessInfo.Id
                    parrRunningWinProcesses.Add(lhtWinProcesses)
                Next
            Else
                For Each ProcessInfo In WordProcesses
                    lintProcessCounter = 0
                    While lintProcessCounter < parrRunningWinProcesses.Count
                        If CType(parrRunningWinProcesses(lintProcessCounter), Hashtable).Item("ProcessID") = ProcessInfo.Id Then
                            Continue For
                        End If
                        lintProcessCounter += 1
                    End While
                    lintProcessCounter = 0
                    While lintProcessCounter < parrNewWinProcesses.Count
                        If CType(parrNewWinProcesses(lintProcessCounter), Hashtable).Item("ProcessID") = ProcessInfo.Id Then
                            Continue For
                        End If
                        lintProcessCounter += 1
                    End While
                    lhtWinProcesses = New Hashtable
                    lhtWinProcesses.Item("ProcessID") = ProcessInfo.Id
                    parrNewWinProcesses.Add(lhtWinProcesses)
                Next
            End If

        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in CountRunningWinProcesses in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        Finally
            WordProcesses = Nothing
            lhtWinProcesses = Nothing
            lintProcessCounter = Nothing
        End Try
    End Sub

    Public Function NewCheckNothingDataTable(ByVal dt As DataTable) As Boolean
        Try
            If dt Is Nothing Then Return True
            If dt Is DBNull.Value Then Return True
            Return False
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in NewCheckNothingDataTable in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
            Throw ex
        End Try
    End Function

    Public Function NewCheckEmptyDataTable(ByVal dt As DataTable) As Boolean

        Try
            If dt Is Nothing Then Return True
            If dt Is DBNull.Value Then Return True
            If dt.Rows.Count = 0 Then Return True
            Return False
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in NewCheckEmptyDataTable in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
            Throw ex
        End Try

    End Function

    Public Sub GetSortedColumnName(ByRef pstrSortedCol As String, ByRef psiGridsSortIndicator As SortIndicator, ByVal pGrid As UltraGrid)
        Try
            If pGrid.DataSource Is Nothing Then Exit Sub
            If pGrid.Rows.Band.SortedColumns.Count > 0 Then
                pstrSortedCol = pGrid.Rows.Band.SortedColumns(0).Key
                psiGridsSortIndicator = pGrid.Rows.Band.SortedColumns(0).SortIndicator
            End If
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in GetSortedColumnName in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try

    End Sub

    Public Sub SetSortedColumnName(ByRef pstrSortedCol As String, ByRef psiGridsSortIndicator As SortIndicator, ByVal pGrid As UltraGrid)
        Try
            If pGrid.DataSource Is Nothing Then Exit Sub
            If pGrid.Rows.Band.Columns.Exists(pstrSortedCol) Then
                pGrid.Rows.Band.Columns(pstrSortedCol).SortIndicator = psiGridsSortIndicator
                If pGrid.Rows IsNot Nothing AndAlso pGrid.Rows.Count > 0 Then
                    pGrid.Rows(0).Selected = True
                    pGrid.Rows(0).Activate()
                End If
            End If
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in SetSortedColumnName in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try

    End Sub

    Public Sub vFormatGrid(ByRef grdExcel As UltraGrid)
        Dim col As UltraGridColumn
        Try
            grdExcel.DisplayLayout.Bands(0).Override.ColumnAutoSizeMode = ColumnAutoSizeMode.AllRowsInBand
            grdExcel.DisplayLayout.Bands(0).Override.AllowRowLayoutCellSpanSizing = Infragistics.Win.Layout.GridBagLayoutAllowSpanSizing.None
            grdExcel.DisplayLayout.Bands(0).Override.CellMultiLine = Infragistics.Win.DefaultableBoolean.False
            'grdExcel.DisplayLayout.Bands(0).Override.CellDisplayStyle = CellDisplayStyle.PlainText
            grdExcel.DisplayLayout.Bands(0).Override.DefaultColWidth = 0
            grdExcel.DisplayLayout.Bands(0).Override.AllowColSizing = AllowColSizing.None


            For Each col In grdExcel.DisplayLayout.Bands(0).Columns
                If col.Hidden = False Then Continue For
                Dim rowCount As Integer = grdExcel.Rows.Count
                Dim calculatedColumnWidth As Integer = col.CalculateAutoResizeWidth(rowCount, True)
                col.MaxLength = calculatedColumnWidth
                col.Width = calculatedColumnWidth
                col.PerformAutoResize(rowCount)
                col.RowLayoutColumnInfo.WeightX = 0
                col.ProportionalResize = True
                col.Format = "AutoFit"
            Next
            ' Dim str As String = grdExcel.DisplayLayout.Bands(0).Override.CellPadding.ToString()
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in vFormatGrid in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        Finally
            col = Nothing
        End Try

    End Sub

    Public Sub SetSortedColumnNameWithoutIndicatorTo0(ByRef pstrSortedCol As String, ByRef psiGridsSortIndicator As SortIndicator, ByVal pGrid As UltraGrid)
        Try
            If pGrid.DataSource Is Nothing Then Exit Sub
            If pGrid.Rows.Band.Columns.Exists(pstrSortedCol) Then
                pGrid.Rows.Band.Columns(pstrSortedCol).SortIndicator = psiGridsSortIndicator
            End If
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in SetSortedColumnNameWithoutIndicatorTo0 in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try

    End Sub

    ''' <summary>
    ''' Kiil the newly  word Process 
    ''' </summary>
    ''' <param name="parrNewWinProcesses"></param>
    ''' <remarks></remarks>
    '''

    Public Sub KillNewWinProcesses(ByVal parrNewWinProcesses As ArrayList)
        Dim WordProcesses() As Process
        Dim ProcessInfo As Diagnostics.Process
        WordProcesses = Process.GetProcessesByName("WINWORD")
        Dim lintProcessCounter As Integer
        Try
            For Each ProcessInfo In WordProcesses
                lintProcessCounter = 0
                While lintProcessCounter < parrNewWinProcesses.Count
                    If CType(parrNewWinProcesses(lintProcessCounter), Hashtable).Item("ProcessID") = ProcessInfo.Id Then
                        Process.GetProcessById(CType(parrNewWinProcesses(lintProcessCounter), Hashtable).Item("ProcessID")).Kill()
                        Continue For
                    Else
                        'MessageBox.Show("Process does not exist anymore.", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Warning)
                    End If
                    lintProcessCounter += 1
                End While
            Next
            'For Each lhtWinProcess As Hashtable In parrNewWinProcesses
            '    While lintProcessCounter < WordProcesses.Length
            '        If Process.GetProcessById(WordProcesses(lintProcessCounter).) = lhtWinProcess.Item("ProcessID") Then
            '            lintProcessCounter += 1
            '            Continue For
            '        End If
            '        lintProcessCounter += 1
            '    End While
            '    Process.GetProcessById(lhtWinProcess.Item("ProcessID")).Kill()
            'Next
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in KillNewWinProcesses in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try
    End Sub

#Region "Data Table"

    'Public Function FindActiveColumnIndex(ByVal ldtColumns As DataTable) As Integer
    '    For Each lrow As DataRow In ldtColumns.Rows
    '        If lrow.Item("Column Name") = "Fund Name" Then
    '            Return ldtColumns.Rows.IndexOf(lrow) + 1
    '        End If
    '    Next
    'End Function

#End Region


#End Region

#Region "Data Table and Dataset"

    ''' <summary>
    ''' checks the dataset is Empty 
    ''' </summary>
    ''' <param name="ds"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function CheckEmptyDataSet(ByVal ds As DataSet) As Boolean

        Try
            If ds Is Nothing Then Return True
            If ds Is DBNull.Value Then Return True
            If ds.Tables.Count = 0 Then Return True
            Return False
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in CheckEmptyDataSet in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
            Throw ex
        End Try

    End Function

    ''' <summary>
    ''' checks the datatable 
    ''' </summary>
    ''' <param name="dtDataTable"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function CheckNothingDataTable(ByVal dtDataTable As DataTable) As Boolean
        Try
            If dtDataTable Is Nothing Then Return False
            If dtDataTable Is DBNull.Value Then Return False
            Return True
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in CheckNothingDataTable in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
            Throw ex
        End Try

    End Function

    Public Function CheckEmptyDataTable(ByVal dtDataTable As DataTable) As Boolean
        Try
            If dtDataTable Is Nothing Then Return True
            If dtDataTable Is DBNull.Value Then Return True
            If dtDataTable.Rows.Count = 0 Then Return True
            Return False
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in CheckEmptyDataTable in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
            Throw ex
        End Try
    End Function

    ''' <summary>
    ''' Add a value in drop down at first place
    ''' </summary>
    ''' <param name="dataTable">Data Table to access</param>
    ''' <param name="idColumn">ID Column of row</param>
    ''' <param name="valueColumn">Text Column of row</param>
    ''' <param name="rowValue">ID Value of row</param>
    ''' <param name="rowText">Text Value of row</param>
    ''' <remarks></remarks>
    Public Function AddRowInDropDownAtFirst(ByRef dataTable As DataTable, ByVal idColumn As String, ByVal valueColumn As String, ByVal rowValue As Integer, ByVal rowText As String) As Boolean
        Try
            If CheckEmptyDataTable(dataTable) Then
                Return False
            End If
            Dim drDataRow As DataRow
            drDataRow = dataTable.NewRow
            drDataRow.Item(idColumn) = rowValue
            drDataRow.Item(valueColumn) = rowText
            dataTable.Rows.InsertAt(drDataRow, 0)
            Return True
        Catch ex As Exception
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in AddRowInDropDownAtFirst in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing

        End Try

    End Function

    ''' <summary>
    ''' Add a value in drop down at last place
    ''' </summary>
    ''' <param name="dataTable">Data Table to access</param>
    ''' <param name="idColumn">ID Column of row</param>
    ''' <param name="valueColumn">Text Column of row</param>
    ''' <param name="rowValue">ID Value of row</param>
    ''' <param name="rowText">Text Value of row</param>
    ''' <remarks></remarks>
    Public Function AddRowInDropDownAtLast(ByRef dataTable As DataTable, ByVal idColumn As String, ByVal valueColumn As String, ByVal rowValue As Integer, ByVal rowText As String) As Boolean
        Try
            If CheckEmptyDataTable(dataTable) Then
                Return False
            End If
            Dim drDataRow As DataRow
            drDataRow = dataTable.NewRow
            drDataRow.Item(idColumn) = rowValue
            drDataRow.Item(valueColumn) = rowText
            dataTable.Rows.Add(drDataRow)
            Return True
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in AddRowInDropDownAtLast in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try

    End Function

    ''' <summary>
    ''' Find the index of a particular [Column Name] from datatable
    ''' </summary>
    ''' <param name="ldtTable">Data Table</param>
    ''' <param name="pstrColumnName">Column Name</param>
    ''' <returns>Column Index</returns>
    ''' <remarks></remarks>
    Public Function FindColumnIndex(ByVal ldtTable As DataTable, ByVal pstrColumnName As String) As Integer
        Try
            Dim lintColumnIndex As Integer = -1
            If ldtTable.Columns.Contains(pstrColumnName) Then
                lintColumnIndex = ldtTable.Columns(pstrColumnName).Ordinal
            End If
            'For Each lcol As DataColumn In ldtTable.Columns
            '    If lcol.ColumnName = pstrColumnName Then
            '        lintColumnIndex = ldtTable.Columns(lcol.ColumnName).Ordinal
            '        Exit For
            '    End If
            'Next
            Return lintColumnIndex
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in FindColumnIndex in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try

    End Function

    ''' <summary>
    ''' Find the index of a particular [Row] from datatable
    ''' </summary>
    ''' <param name="ldtTable">Data Table</param>
    ''' <param name="pstrColumnName">Column Name</param>
    ''' <returns>Column Index</returns>
    ''' <remarks></remarks>
    Public Function FindRowIndex(ByVal ldtTable As DataTable, ByVal pstrColumnName As String, ByVal pstrValue As String) As Integer
        Try
            Dim lintRowIndex As Integer = -1
            Dim isNumeric As Boolean = False
            Dim pstrVal As Integer = 0
            isNumeric = Integer.TryParse(pstrValue, pstrVal)
            If isNumeric Then
                For Each lRow As DataRow In ldtTable.Rows
                    If lRow.Item(pstrColumnName) = pstrVal Then
                        lintRowIndex = ldtTable.Rows.IndexOf(lRow)
                        Exit For
                    End If
                Next
            Else
                For Each lRow As DataRow In ldtTable.Rows
                    If lRow.Item(pstrColumnName) = pstrValue Then
                        lintRowIndex = ldtTable.Rows.IndexOf(lRow)
                        Exit For
                    End If
                Next
            End If
            Return lintRowIndex
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in FindRowIndex in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try

    End Function

    Public Sub RemoveColumn(ByRef pdtTable As DataTable, ByVal pstrColName As String)
        Try
            If pdtTable.Columns.Contains(pstrColName) Then
                pdtTable.Columns.Remove(pstrColName)
            End If
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in RemoveColumn in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try

    End Sub

    Public Sub SetReportAndSourceOrdinal(ByRef pdtTable As DataTable, ByVal pstrColName As String, ByVal pintColIndex As Integer)
        Try
            If pdtTable.Columns.Contains(pstrColName) Then
                pdtTable.Columns(pstrColName).SetOrdinal(pintColIndex)
            End If
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in SetReportAndSourceOrdinal in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
        End Try

    End Sub

#End Region

#Region "Infragistics"

#Region "Ultra Grid"

    ''' <summary>
    ''' Check if DataSource has Rows and is not nothing
    ''' </summary>s
    ''' <param name="grdGrid">Grid to be checked</param>
    ''' <returns>Status (Yes/No)</returns>
    ''' <remarks></remarks>
    Public Function IsDataSourceWithRows(ByVal grdGrid As UltraGrid) As Boolean
        If grdGrid.DataSource IsNot Nothing Then
            If grdGrid.Rows.Count > 0 Then
                Return True
            Else
                Return False
            End If
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Check if a column exists in grid
    ''' </summary>
    ''' <param name="drUltraGridRow">Grid Row</param>
    ''' <param name="strColumnName">Column Name</param>
    ''' <returns>Status (Yes/No)</returns>
    ''' <remarks></remarks>
    Public Function IsColumnExistsInUltraGridRow(ByVal drUltraGridRow As UltraGridRow, ByVal strColumnName As String) As Boolean
        If drUltraGridRow.Band.Columns.Exists(strColumnName) Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Check Active Cell, Active Row and Rows of Ultragrid for NOTHING
    ''' </summary>
    ''' <param name="grdUltraGrid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function CheckNothingUltraGridActiveCell(ByVal grdUltraGrid As UltraGrid) As Boolean
        Try
            If grdUltraGrid.Rows Is Nothing Then Return False
            If grdUltraGrid.ActiveRow Is Nothing Then Return False
            If grdUltraGrid.ActiveRow.Cells Is Nothing Then Return False
            Return True
        Catch ex As Exception
            Throw ex
        End Try
    End Function

#End Region

#Region "UltraCombo"

    ''' <summary>
    ''' Check Data Source, Selected Row Ultracombo
    ''' </summary>
    ''' <param name="cmbUltracombo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function CheckNothingUltraCombo(ByVal cmbUltracombo As UltraCombo) As Boolean
        Try
            If cmbUltracombo.DataSource Is Nothing Then Return False
            'If cmbUltracombo.SelectedRow Is Nothing Then Return False
            Return True
        Catch ex As Exception
            Throw ex
        End Try
    End Function

#End Region

#Region "List box"

    ''' <summary>
    ''' Check Data Source and Selected Item, Selected Row ListBox
    ''' </summary>
    ''' <param name="lstListBox"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function CheckSelectedItemInListBox(ByVal lstListBox As ListBox) As Boolean
        Try
            If lstListBox.DataSource Is Nothing Then Return False
            If lstListBox.SelectedItem Is Nothing Then Return False
            Return True
        Catch ex As Exception
            Throw ex
        End Try
    End Function

#End Region

#Region "Text Editor"

    ''' <summary>
    ''' This method set the control disable
    ''' </summary>
    ''' <param name="val"></param>
    ''' <param name="b"></param>
    ''' <remarks></remarks>
    Public Sub DisableAllButtons(ByVal val As Boolean, ByVal ParamArray b() As System.Windows.Forms.Control)
        Try
            For i As Integer = 0 To b.Length - 1
                If b(i).GetType.Name = "UltraTextEditor" Then
                    CType(b(i), Infragistics.Win.UltraWinEditors.UltraTextEditor).ReadOnly = Not val
                    b(i).Enabled = val
                    'CType(b(i), Infragistics.Win.UltraWinEditors.UltraTextEditor).t = Not val
                Else
                    b(i).Enabled = val
                End If
            Next
            'txtsearch.ReadOnly = Not val
        Catch ex As Exception
            Throw ex
        End Try

    End Sub
#End Region

#End Region

    ''' <summary>
    '''Sort data table 
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="str">Expression as string</param>
    ''' <remarks></remarks>
    <Extension()> _
    Public Sub Sort(ByRef dt As DataTable, ByVal str As String)
        Dim dv As DataView
        Try
            dv = New DataView(dt)
            dv.Sort = str
            dt = dv.ToTable()
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in Sort in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
            Throw ex
        Finally
            dv = Nothing
        End Try
    End Sub

    Public Sub FormatGrid(ByRef grdExcel As UltraCombo)
        Dim GridCol As UltraGridColumn
        Try
            Dim rowCount As Integer = grdExcel.Rows.Count
            If grdExcel.Rows.Count = 0 Then
                Exit Sub
            End If
            grdExcel.DisplayLayout.Bands(0).Override.ColumnAutoSizeMode = ColumnAutoSizeMode.AllRowsInBand
            grdExcel.DisplayLayout.Bands(0).Override.AllowRowLayoutCellSpanSizing = Infragistics.Win.Layout.GridBagLayoutAllowSpanSizing.None
            grdExcel.DisplayLayout.Bands(0).Override.CellMultiLine = Infragistics.Win.DefaultableBoolean.False
            grdExcel.DisplayLayout.Bands(0).Override.DefaultColWidth = 0
            For Each GridCol In grdExcel.DisplayLayout.Bands(0).Columns
                GridCol.PerformAutoResize(rowCount)
                Application.DoEvents()
            Next
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in FormatGrid in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
            Throw ex
        Finally
            GridCol = Nothing
        End Try
    End Sub

    Public Function GetDecimalValue(ByVal str As String) As String
        Try
            If Not str.Contains(".") And Not str = "" And Not str = "0" And Not str = "0.00" Then
                Return str + ".00"
            Else
                If str.Contains(".") And Not str = "0" And Not str = "0.00" Then
                    If str.IndexOf(".") = str.Length - 1 Then
                        Return str + "00"
                    Else
                        If str.IndexOf(".") = str.Length - 2 And Not str = "0" And Not str = "0.00" Then
                            Return str + "0"
                        Else
                            Return str
                        End If
                    End If
                End If
            End If
            Return ""
        Catch ex As Exception
            'Varun Thakur | 21/08/2009 
            ''Logging
            Dim MsgLog As New System.Text.StringBuilder
            MsgLog.Append("There is error in GetDecimalValue in mdlCommon")
            MsgLog.Append(Environment.NewLine)
            MsgLog.Append("Error Details:- ")
            MsgLog.Append(ex.ToString())
            CreateErrorLog(MsgLog)
            MsgLog = Nothing
            Throw ex
        End Try
    End Function
    Public Sub DisposeControls(ByRef PName As Form)
        'Dim iCount As Integer
        'Dim _Parent As String = ""
        ''Dim _iChildCount As Integer
        ''Dim _iCCCount As Integer
        '_Parent = PName.Name
        'For iCount = 0 To PName.Controls.Count - 1
        '    If PName.Controls(PName.Controls.Count - 1).HasChildren Then
        '        PName.Controls(PName.Controls.Count - 1).
        '    End If
        '    PName.Controls(PName.Controls.Count - 1).Dispose()
        'Next
    End Sub

    Public Sub ShowMessage(ByVal message As String)
        MessageBox.Show(message, mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

#Region "Data Reader Read Only"

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="ChkTable"></param>
    ''' <remarks></remarks>
    Public Sub ChangeDataReaderDataSet(ByRef ChkTable As DataSet)
        For Each dttable As DataTable In ChkTable.Tables
            For Each lCol As DataColumn In dttable.Columns
                lCol.ReadOnly = False
                lCol.AllowDBNull = True
            Next
        Next
    End Sub

    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="ChkTable"></param>
    ''' <remarks></remarks>
    Public Sub ChangeDataReaderDataTable(ByRef ChkTable As DataTable)
        For Each lCol As DataColumn In ChkTable.Columns
            lCol.ReadOnly = False
            lCol.AllowDBNull = True
        Next
    End Sub
#End Region

#Region "Excel Import Process"
    Public Function GetDataFromExcel(ByVal isColumDupliacte As Boolean, ByVal SheetPath As String, ByVal _SheetName As String, ByRef dtInvestor As DataTable) As DataTable
        'Create the temporary table to store data
        'Dim dtInvestor As New DataTable()
        Dim myDataColumn As DataColumn
        Dim myDataRow As DataRow
        Dim index As Integer = 0
        'MIN/MAX Values used to frame the working size of the Excel data to be imported.
        Dim minCellRow As Integer = Int32.MaxValue
        Dim maxCellRow As Integer = Int32.MinValue
        Dim minCellColumn As Integer = Int32.MaxValue
        Dim maxCellColumn As Integer = Int32.MinValue

        Dim internalWorkBook As Infragistics.Documents.Excel.Workbook = Nothing
        internalWorkBook = Infragistics.Documents.Excel.Workbook.Load(SheetPath)
        If internalWorkBook.Worksheets(_SheetName).Rows.Count > 0 Then
            For Each row As Infragistics.Documents.Excel.WorksheetRow In internalWorkBook.Worksheets(_SheetName).Rows
                For Each cell As Infragistics.Documents.Excel.WorksheetCell In row.Cells
                    If cell.Value IsNot Nothing Then
                        'Logic For Determining the Range of Rows/Columns in the Excel File.
                        minCellRow = Math.Min(minCellRow, cell.RowIndex)
                        maxCellRow = Math.Max(maxCellRow, cell.RowIndex)
                        minCellColumn = Math.Min(minCellColumn, cell.ColumnIndex)
                        maxCellColumn = Math.Max(maxCellColumn, cell.ColumnIndex)
                    End If
                Next
            Next
            If isColumDupliacte = True Then
                For i As Integer = minCellColumn To maxCellColumn
                    'Get the column name
                    Dim columnName As String = internalWorkBook.Worksheets(_SheetName).Rows(minCellRow).Cells(i).Value.ToString()
                    'The export that was demonstrated earlier utilizes the first row
                    'for the column header.  We can now use that to give column names.
                    myDataColumn = New DataColumn(columnName)
                    If internalWorkBook.Worksheets(_SheetName).Rows(minCellRow).Cells(i).CellFormat.FormatString.Contains("m/d/yy") Then
                        myDataColumn.DataType = GetType(Date)
                        dtInvestor.Columns.Add(myDataColumn)
                    Else
                        dtInvestor.Columns.Add(myDataColumn)
                    End If

                Next
            Else
                For i As Integer = minCellColumn To maxCellColumn
                    Dim columnName As String = internalWorkBook.Worksheets(_SheetName).Rows(minCellRow).Cells(i).Value.ToString()
                    'The export that was demonstrated earlier utilizes the first row
                    'for the column header.  We can now use that to give column names.
                    myDataColumn = New DataColumn(columnName)
                    Dim str As String = myDataColumn.ColumnName
                    If str.ToLower.Contains("domicile") Then
                        myDataColumn = New DataColumn("Country name")
                    End If
                    dtInvestor.Columns.Add(myDataColumn)
                Next


                For rowIndex As Integer = minCellRow + 1 To maxCellRow
                    'Create a new DataRow
                    myDataRow = dtInvestor.NewRow()
                    Dim myDate As DateTime = #1/1/1900 12:00:00 PM#
                    Dim myFormat As String = "MM/DD/YYYY"
                    Dim stringValue As String = String.Empty
                    Dim isDate As Boolean = False
                    'Loop through the columns and associate the value to each cell
                    For columnIndex As Integer = minCellColumn To maxCellColumn
                        stringValue = Convert.ToString((internalWorkBook.Worksheets(_SheetName).Rows(rowIndex).Cells(columnIndex).Value))
                        If internalWorkBook.Worksheets(_SheetName).Rows(rowIndex).Cells(columnIndex).CellFormat.FormatString IsNot Nothing Then
                            If internalWorkBook.Worksheets(_SheetName).Rows(rowIndex).Cells(columnIndex).CellFormat.FormatString.Contains("m/d/yy") Then
                                index = columnIndex
                                'myFormat = internalWorkBook.Worksheets(_SheetName).Rows(rowIndex).Cells(columnIndex).CellFormat.FormatString
                                'myFormat = "M/d/yyyy" 'myFormat.Replace("m", "M")
                                myDate = myDate.AddDays(internalWorkBook.Worksheets(_SheetName).Rows(rowIndex).Cells(columnIndex).Value - 2).ToString("d")
                                stringValue = String.Empty
                                isDate = True
                                'myDate = Date.Parse(myDate.ToString(myFormat))
                            End If
                        End If
                        If stringValue Is Nothing Or stringValue = String.Empty Then
                            If isDate Then
                                myDataRow(columnIndex) = myDate
                                isDate = False
                            Else
                                myDataRow(columnIndex) = DBNull.Value
                            End If
                        Else
                            myDataRow(columnIndex) = stringValue.ToString.Trim
                        End If
                    Next
                    'Add The Row to a DataTable
                    dtInvestor.Rows.Add(myDataRow)
                Next
            End If
        Else
            isColumDupliacte = False
        End If
        Return dtInvestor

    End Function



    ''' <summary>
    ''' To get the Investor Excel File data in the global Datatable(mdtExcel)
    ''' </summary>
    ''' <param name="IsColumDupliacte"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetExcelFileData(ByRef isColumDuplicate As Boolean, ByVal isFromFormat As Boolean, ByRef dtExcel As DataTable, ByVal SheetPath As String, ByVal SheetName As String, ByRef _TotalExcelRows As Integer) As Boolean

        Dim ColName As String = ""
        Dim DtInvestor As New DataTable
        Dim Count As Integer
        Dim ExcelHandling As New ExcelHandling()
        Try
            GetDataFromExcel(isColumDuplicate, SheetPath, SheetName, DtInvestor)
            'ExcelHandling.GetDataFromExcel(isFromFormat, Trim(txtAttach.Text), _SheetName, DSInvestor)
            If Not (Crystal.CLL.CommonCLL.CheckDuplicateColumn(ColName, SheetPath.Trim, DtInvestor, SheetName, isColumDuplicate)) Then
                'If Not (ExcelHandling.CheckDuplicateColumn(ColName, txtAttach.Text.Trim, DtInvestor, _SheetName, isColumDupliacte)) Then
                MessageBox.Show("Column '" + ColName & "' is duplicate in file", mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Return False
            End If

            isColumDuplicate = False
            If isFromFormat = True Then
                dtExcel = DtInvestor.Clone
            Else
                dtExcel = DtInvestor.Copy()

            End If
            _TotalExcelRows = dtExcel.Rows.Count


            While Count < dtExcel.Columns.Count
                dtExcel.Columns(Count).ColumnName = DtInvestor.Columns(Count).ColumnName
                Count += 1
            End While

            If dtExcel.Columns.Count > 0 Then
                Return True
            Else
                Return False
            End If

        Catch ex As Exception
            '''''Logging
            Dim Logmessage As New System.Text.StringBuilder
            Logmessage.Append("There is error in GetExcelFileData of form import investor.")
            Logmessage.Append(Environment.NewLine)
            Logmessage.Append("Error Message - ")
            Logmessage.Append(ex.ToString())
            CreateErrorLog(Logmessage)
            Logmessage = Nothing
            ''''''''''''''''''''''''''''
            Return False

        Finally
            DtInvestor = Nothing
            ExcelHandling = Nothing
        End Try

    End Function

    ''' <summary>
    ''' this the function use to get Mapped columns name and inseting in dtExcelClone, For Mapping Refrences
    ''' </summary>
    ''' <param name="dtInvestor"></param>
    ''' <param name="dtExcelClone"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    ''' 

    Public Function MapWithDatabaseColumns(ByVal formatID As Integer, ByRef dtInvestor As DataTable, ByRef dtExcelClone As DataTable) As Boolean

        Dim ExcelColName As String
        Dim ReturnVal As ExecutionResult
        Dim MappedCol As DataTable = Nothing
        Dim ExcelClone As DataRow
        Dim ColumnName As New ArrayList
        Dim idr As IDataReader = Nothing
        Dim Excel As DataTable
        Try
            dtExcelClone = New DataTable
            dtExcelClone = dtInvestor.Clone()
            dtExcelClone.Columns.Add("FileName", GetType(String))
            ExcelClone = dtExcelClone.NewRow
            Excel = dtInvestor.Copy()

            ReturnVal = New ExecutionResult
            MappedCol = New DataTable
            For Each Col As DataColumn In Excel.Columns
                ExcelColName = Col.ColumnName
                MappedCol = Nothing
                MappedCol = New DataTable
                idr = BLLmdlCommon.MapDatabaseColumn(formatID, ExcelColName)
                MappedCol.Load(idr)
                If (MappedCol.Rows(0).Item("FieldName") <> "UNMAPPED") Then

                    ExcelClone.Item("FileName") = Col.ColumnName
                    If dtInvestor.Columns(Col.ColumnName).ColumnName <> MappedCol.Rows(0).Item("FieldName") Then
                        If dtInvestor.Columns.Contains(MappedCol.Rows(0).Item("FieldName")) Then
                            dtInvestor.Columns(Col.ColumnName).ColumnName = MappedCol.Rows(0).Item("FieldName") & "~"
                            ColumnName.Add(MappedCol.Rows(0).Item("FieldName"))
                        Else
                            dtInvestor.Columns(Col.ColumnName).ColumnName = MappedCol.Rows(0).Item("FieldName")
                        End If
                    Else
                        dtInvestor.Columns(Col.ColumnName).ColumnName = MappedCol.Rows(0).Item("FieldName")
                    End If
                    dtExcelClone.Rows.Add(ExcelClone)
                    ExcelClone = dtExcelClone.NewRow
                Else
                    If Col.ColumnName <> "FileName" Then
                        dtExcelClone.Columns.Remove(Col.ColumnName)
                    End If
                    dtInvestor.Columns.Remove(Col.ColumnName)
                End If
            Next

            For Each lstrColumnName As String In ColumnName
                dtInvestor.Columns(lstrColumnName & "~").ColumnName = lstrColumnName
            Next
        Catch ex As Exception
        Finally
            idr = Nothing
        End Try
        Return True

    End Function

    Public Function IsFieldsMapped(ByVal pdtExcelClone As DataTable, ByVal _FormatID As String) As Boolean
        Dim ReturnVal As New ExecutionResult
        Dim MappedFields As New DataTable
        Dim Query As String
        Dim NotMappedField As New System.Text.StringBuilder
        Dim MappedField() As DataRow
        Dim IDR As IDataReader = Nothing
        Try
            IDR = BLLmdlCommon.GetMappedColumns(_FormatID)
            MappedFields.Load(IDR)
            If CommonCLL.CheckEmptyDataTable(MappedFields) Then
                ShowMessage("Please map mandatory fields before import.")
                Return False
            End If
            For Each drRow As DataRow In MappedFields.Rows
                Query = "FileName='" & Trim(drRow.Item("TFieldName")).ToString.Replace("'", "''") & "'"
                MappedField = pdtExcelClone.Select(Query)
                If MappedField.Length > 0 Then
                Else
                    If NotMappedField.ToString() <> "" Then
                        NotMappedField.Append(vbNewLine)
                    End If
                    NotMappedField.Append(Trim(drRow.Item("TFieldName")))

                End If
            Next
            If NotMappedField.ToString() <> "" Then
                ShowMessage("Following column(s) name does not exist in the selected format. " & vbNewLine & NotMappedField.ToString()) ', mstrMsgBoxCaption, MessageBoxButtons.OK, MessageBoxIcon.Information)
                Return False
            End If
        Catch ex As Exception
            Throw ex
            Return False
        Finally
            NotMappedField = Nothing
            MappedFields = Nothing
            ReturnVal = Nothing
            NotMappedField = Nothing
            MappedField = Nothing
        End Try
        Return True
    End Function

#End Region
#Region "Grid Event"

    ''' <summary>
    ''' assign the text to label control on grid click
    ''' </summary>
    ''' <remarks></remarks>
    Public Function GridClick(ByVal lintMessage As Integer) As String

        'If grdNotImported.ActiveRow Is Nothing Then Exit Sub
        'If lintMessage Is DBNull.Value Then Exit Sub
        If lintMessage = BLLmdlCommon.GridRecord.InvestorEntityIDExistsInFile Then
            Return "Investor ID exists multiple times."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvestorEntityIDExistsInDB Then
            Return "Investor ID already exists in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.NumericInvestorName Then
            Return "Investor name cannot be numeric. "
        ElseIf lintMessage = BLLmdlCommon.GridRecord.Numericstate Then
            Return "State name cannot be numeric. "
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvestorNameBlank Then
            Return "Investor name cannot be blank. "
        ElseIf lintMessage = BLLmdlCommon.GridRecord.EntityBlank Then
            Return "EntityID cannot be blank."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.Countryblank Then
            Return "Country cannot be blank."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.CountryNotExists Then
            Return "Country does not exist in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.Regionblank Then
            Return "Region cannot be blank."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.RegionNotExists Then
            Return "Region does not exist in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StyleNotExists Then
            Return "Style does not exist in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StateNotExists Then
            Return "State does not exist in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TypeNotExistsInMDB Then
            Return "Type does not exist in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TypeFundExists Then
            Return "Investor type with 'Fund' or 'Market Ltd Part' cannot be imported."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.CityBlank Then
            Return "City is blank OR contains * or **."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.DateNotWellFormated Then
            Return "Date not well formated. "
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StateLengthNot2 Then
            Return "State value length is not 2. "
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StatusNotExists Then
            Return "Status does not exist in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvestorNameExistsInFile Then
            Return "Investor Name exists multiple times."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.InvestorNameExistsInDB Then
            Return "Investor Name already exists in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TypeBlank Then
            Return "Type can not be left blank."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.StyleBlank Then
            Return "Style can not be left blank."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.MasterInvestorTypeBlank Then
            Return "Master investor type can not be left blank."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TypeLinkingNotExists Then
            Return "Linking of investor type and master investor type does not exist in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TurnoverNotExistsInDB Then
            Return "Turnover does not exist in the database."
        ElseIf lintMessage = BLLmdlCommon.GridRecord.TurnoverBlank Then
            Return "Turnover can not be left blank."
        End If
        'MasterTypeMultipleExistsInDB
        Return ""
    End Function

#End Region


End Module
'End Namespace

